In the field of database technology, resources, generally in the form of a row within a table, are accessed to be written to or read from. In most cases, the resource is individually accessed by queued transactions which are arranged in a First-In-First-Out (FIFO) manner. At any time, the resource may be accessed by multiple transactions which have concurrently requested access to the resource provided the transactions have compatible access modes.
To maintain the integrity of the resource being accessed, as well as to prevent other non-compatible transactions from accessing the resource, a locking protocol is maintained. Furthermore, any existing transaction requests have a requirement that new transaction requests should be aware that the existing transaction requests are currently stored in the database. All new transaction requests should be made aware that there are existing transaction requests.
Moreover, the new transaction requests have to wait for the existing transaction requests to finish executing. Each of the transaction requests contains information corresponding to a lock request as along with other relevant information.
Locking refers to the device associated with a transaction request having access to the resource. Therefore, when a transaction has a lock, it has been granted access to the resource. If a transaction request requests the lock but has an access mode (share, exclusive, etc.) that is not compatible with the access modes of the transactions holding the lock (executing transactions), it is forced to wait. In this case, the lock request is created for the transaction request and added to the end of a list of transactions (which includes those that have a hold on the lock and those waiting to be granted). The transaction request contains all the information pertaining to this lock request.
When a transaction request that has been granted the lock (access to the resource) has executed, the transaction request releases its hold on the lock. The list of transactions is reassessed and the next transaction waiting in line may be granted the lock if its access mode is compatible with the other executing transactions.
This reassessment continues down the list until an access mode, which is not compatible with the executing transactions is found. If there is a transaction further down the list that is compatible with the access mode of the executing transactions, it will have to wait until the transactions before it have been granted the lock. As discussed earlier, this causes the data transactions to be handled in a FIFO order.
It is important to maintain the FIFO order to prevent starvation. Starvation occurs when a transaction has to continuously wait for and is never granted the lock (access to the resource) since it allows other transactions with different access modes to pass it.
For example, if a row in a table is to be updated while there is a transaction reading from the row, the update may not take place until all the transaction requests currently accessing the resource have finished executing. To address this problem, presently, an instant lock request having an access mode, preferably a strong access mode, that conflicts with access modes of the executing transactions is sent. By using a strong access mode, the subsequent transaction request enters a converting state and the subsequent transaction request is granted the lock when all current transactions have released their lock on the resource. However, by using the strong access mode new scanners are prevented from reading from the table. This can cause major performance problems as no other work can be done on this table while waiting for the scanners to complete reading.
What is therefore needed is a system and associated method for obviating or mitigating the foregoing problem.